{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Lecture 3B - Data Integration*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "* [Lecture 3B - Data Integration*](#Lecture-12---Data-Integration*)\n",
    "\t* &nbsp;\n",
    "\t* [Content](#Content)\n",
    "\t* [Learning Outcomes](#Learning-Outcomes)\n",
    "\t* [Integration of data from multiple sources](#Integration-of-data-from-multiple-sources)\n",
    "\t* [Merging Datasets](#Merging-Datasets)\n",
    "\t\t* [Database-style DataFrame Merges](#Database-style-DataFrame-Merges)\n",
    "\t\t* [Merging on Index](#Merging-on-Index)\n",
    "\t\t* [Concatenating Data Frames](#Concatenating-Data-Frames)\n",
    "\t\t* [Concatenation on axes](#Concatenation-on-axes)\n",
    "\t\t* [Updating Columns](#Updating-Columns)\n",
    "\t\t\t* [Combining together values within Series or DataFrame columns from different sources](#Combining-together-values-within-Series-or-DataFrame-columns-from-different-sources)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. dataset merging\n",
    "2. dataset concatenation\n",
    "3. dataset value updating\n",
    "\n",
    "\n",
    "\\* Content in this notebook is based on the material in the \"Python for Data Analysis\" book by Wes McKinney, chapter 7. and material from http://pandas.pydata.org/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning Outcomes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At the end of this lecture, you should be able to:\n",
    "\n",
    "* describe the inner, outer, left, right join-types for merging dataframes \n",
    "* merge different dataframes on indices or common columns\n",
    "* concatenate dataframes horizontally or vertically\n",
    "* update values in one dataframe based on values from a similar dataframe \n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML, IFrame\n",
    "IFrame(\"http://pandas.pydata.org/pandas-docs/dev/merging.html\", width=1100, height=500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Integration of data from multiple sources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Much of the work in the overall analytics pipeline is spent on data preparation: loading, cleaning, transforming, and rearranging. The total time spent on this task can be up to 90% of the entire analytics project time resources, before any actual useful 'analytics' work is done.\n",
    "\n",
    "Increasingly datasets from multiple sources must be integrated into a single dataset. This can be a difficult task especially if done manually through Excel-type programs. In many cases it is impossible due to file size, and often undesirable to to the fact that it is difficult to document the process and also impossible to audit and repeat automatically.\n",
    "\n",
    "Many analytics professionals choose to do ad hoc processing of data from one form to another using a general purpose programming, like Python, Perl, R, or Java, or UNIX text processing tools like sed or awk. \n",
    "\n",
    "Fortunately, pandas along with the Python standard library provide you with a high-level, flexible, and high-performance set of core manipulations and algorithms to enable you to integrate and wrangle data into a single source without much trouble."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data contained in pandas objects can be combined together in a number of built-in ways:\n",
    "\n",
    "* `pandas.merge` connects rows in DataFrames based on one or more keys. This will be familiar to users of SQL or other relational databases, as it implements database join operations.\n",
    "* `pandas.concat` glues or stacks together objects along an axis (`axis=1` columns, `axis=0` rows)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database-style DataFrame Merges"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge or join operations combine data sets by linking rows using one or more **keys**.\n",
    "These operations are central to relational databases. \n",
    "\n",
    "The `merge` function in pandas is\n",
    "the main entry point for using these algorithms on your data.\n",
    "\n",
    "\n",
    "We will begin with simple examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from pylab import rcParams\n",
    "\n",
    "# Set some Pandas options as you like\n",
    "pd.set_option('max_columns', 30)\n",
    "pd.set_option('max_rows', 30)\n",
    "\n",
    "rcParams['figure.figsize'] = 15, 10\n",
    "rcParams['font.size'] = 20\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(\n",
    "                    {'name': ['ben', 'ben', 'adam', 'cindy', 'adam', 'adam', 'ben'],\n",
    "                     'transaction': np.random.randint(1, 50, 7)}\n",
    "                  )\n",
    "\n",
    "df2 = pd.DataFrame(\n",
    "                    {'name': ['adam', 'ben', 'darren'],\n",
    "                     'age': [33,25,40]}\n",
    "                )\n",
    "print(df1)\n",
    "print('---------------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is an example of a many-to-one merge situation using the `pandas.merge` method; the data in `df1` has multiple rows\n",
    "labelled 'adam' and 'ben', whereas `df2` has only one row for each value in the key column. Calling\n",
    "merge with these objects we obtain:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we **did not specify** which column to join on. If not specified, merge uses the\n",
    "**overlapping column names as the keys**. It is however good practice to specify explicitly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, on='name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that original indexes cannot be preserved when merging on columns.\n",
    "\n",
    "If the column names are different in each object, you can specify them separately:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame(\n",
    "                {'lkey': ['ben', 'ben', 'adam', 'cindy', 'adam', 'adam', 'ben'],\n",
    "                 'data1': np.random.randint(1, 50, 7)}\n",
    "                )\n",
    "\n",
    "df4 = pd.DataFrame(\n",
    "                {'rkey': ['adam', 'ben', 'darren'],\n",
    "                 'age': [33,25,40]}\n",
    "                )\n",
    "\n",
    "print(df3)\n",
    "print('---------')\n",
    "print(df4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df3, df4, left_on='lkey', right_on='rkey')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You probably noticed that the 'cindy' and 'darren' values and associated data are missing from\n",
    "the result. \n",
    "\n",
    "**By default merge does an 'inner' join**; the keys in the result are the **intersection** of the two sets.\n",
    "Other possible options are 'left', 'right', and 'outer'. \n",
    "\n",
    "The outer join takes the\n",
    "union of the keys, combining the effect of applying both left and right joins:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df1)\n",
    "print('---------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above merges have been examples of **one-to-many and many-to-one merges**. Sometimes it is necessary to perform **one-to-one merges on indexes**, these we perform on indexes and we will see them later.\n",
    "\n",
    "Many-to-many merges have well-defined though not necessarily intuitive behaviour.\n",
    "\n",
    "Here’s an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(\n",
    "                {'name': ['ben', 'ben', 'adam', 'cindy', 'adam', 'ben'],\n",
    "                 'transaction_1': range(6)}\n",
    "                )\n",
    "df2 = pd.DataFrame(\n",
    "                {'name': ['adam', 'ben', 'adam', 'ben', 'darren'],\n",
    "                 'transaction_2': range(5)}\n",
    "                )\n",
    "print(df1)\n",
    "print('---------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, on='name', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Many-to-many joins form the **Cartesian product** of the rows. Since there were 3 'ben'\n",
    "rows in the left DataFrame and 2 in the right one, there are 6 'ben' rows in the result.\n",
    "\n",
    "The join method only affects the distinct key values appearing in the result. \n",
    "\n",
    "By this we mean that if there are unique keys in either left or right hand side, the type of join method will determine if rows with the unique values appear in the final result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercises**: For the following exercises, use the 3 data sets below (source http://www.goldpriceoz.com/). The datasets below represent the \"Gold Price Annual End of Period\" for a selection of currencies.\n",
    "\n",
    "Create dataframes from the datasets below by highlighting the dataset and right-clicking copy, followed by the execution of the following line: \n",
    "\n",
    "df = pd.read_clipboard() "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "DATAFRAME 1\n",
    "\n",
    "ID Year\n",
    "0 \t1999\n",
    "1 \t2000\n",
    "2 \t2001\n",
    "3 \t2002\n",
    "4 \t2003\n",
    "5 \t2004\n",
    "6 \t2005\n",
    "7 \t2006\n",
    "8 \t2007\n",
    "9 \t2008\n",
    "10 \t2009\n",
    "11 \t2010\n",
    "12 \t2011\n",
    "13 \t2012\n",
    "14 \t2013\n",
    "15 \t2014\n",
    "\n",
    "DATAFRAME 2 (df_USD)\n",
    "\n",
    "ID   USD \tEUR \tGBP\n",
    "0 \t290.2 \t289.6 \t180.1\n",
    "1 \t274.5 \t292.3 \t183.7\n",
    "2 \t276.5 \t310.5 \t189.9\n",
    "3 \t347.2 \t330.9 \t215.7\n",
    "4 \t416.3 \t330.0 \t232.5\n",
    "5 \t435.6 \t320.5 \t226.9\n",
    "6 \t513.0 \t434.9 \t298.8\n",
    "7 \t632.0 \t479.3 \t322.9\n",
    "8 \t833.8 \t570.3 \t418.8\n",
    "9 \t869.8 \t625.7 \t604.9\n",
    "10 \t1087.5 \t757.9 \t673.4\n",
    "11 \t1405.5 \t1047.7 \t897.7\n",
    "12 \t1531.0 \t1179.4 \t985.1\n",
    "13 \t1657.5 \t1257.2 \t1019.7\n",
    "14 \t1204.5 \t871.9 \t728.9\n",
    "15 \t1182.4 \t1092.8 \t790.8\n",
    "\n",
    "DATAFRAME 3 (df_AUD)\n",
    "\n",
    "ID AUD CNY INR\n",
    "0 \t443.5 \t2403.1 \t12630.9\n",
    "1 \t493.9 \t2271.7 \t12811.3\n",
    "2 \t540.1 \t2288.5 \t13332.8\n",
    "3 \t616.6 \t2873.8 \t16648.2\n",
    "4 \t552.5 \t3445.2 \t18991.4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Your first task is to merge the Year End Period data with each of the 2 datasets containing the period end price of gold. Call them df_USD and df_AUD."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_y = pd.read_clipboard()\n",
    "df_y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_USD = pd.read_clipboard()\n",
    "df_USD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_AUD = pd.read_clipboard()\n",
    "df_AUD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Merge df_USD and df_AUD so that only common data to both datasets is preserved in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Merge df_USD and df_AUD so that all data in df_USD is preserved in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Merge df_USD and df_AUD so that all data in df_AUD is preserved in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Merge df_USD and df_AUD so that all data from both datasets is preserved in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Plot the price of gold for one of the currencies, for each of the years in the dataset using an appropriate figure type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can merge with multiple keys. To merge with multiple keys, pass a list of column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame(\n",
    "                 {'key1': ['foo', 'foo', 'bar'],\n",
    "                 'key2': ['one', 'two', 'one'],\n",
    "                 'lval': [1, 2, 3]}\n",
    "                 )\n",
    "\n",
    "right = pd.DataFrame(\n",
    "                  {'key1': ['foo', 'foo', 'bar', 'bar'],\n",
    "                   'key2': ['one', 'one', 'one', 'two'],\n",
    "                   'rval': [4, 5, 6, 7]}\n",
    "                  )\n",
    "print(left)\n",
    "print('---------')\n",
    "print(right)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on=['key1', 'key2'], how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To determine which key combinations will appear in the result depending on the choice\n",
    "of merge method, **think of the multiple keys as forming an array of tuples to be used\n",
    "as a single join key**.\n",
    "\n",
    "When joining columns-on-columns, the **indexes on the passed Data Frame objects are discarded**.\n",
    "\n",
    "A last issue to consider in merge operations is the treatment of overlapping column\n",
    "names. While you can address the overlap manually, merge has a suffixes option for specifying strings to append to overlapping\n",
    "names in the left and right DataFrame objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on='key1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the suffixes '_x' and '_y' above which are default. We can explicitly specify them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on='key1', suffixes=('_left', '_right'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Given the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = pd.DataFrame(\n",
    "                 {'key1': ['foo', 'foo', 'bar'],\n",
    "                 'key2': ['one', 'two', 'one'],\n",
    "                 'val': [1, 2, 3]}\n",
    "                 )\n",
    "\n",
    "df6 = pd.DataFrame(\n",
    "                  {'key1': ['one', 'one', 'one', 'two'],\n",
    "                   'key2': ['foo', 'foo', 'bar', 'bar'],\n",
    "                   'val': [4, 5, 6, 7]}\n",
    "                  )\n",
    "print(df5)\n",
    "print('---------')\n",
    "print(df6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your task is to merge on key1 from df5 and key2 from df6 using a merge type that preserves all unique keys, and renaming overlapping columns with the '_l' and '_r' suffixes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging on Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In some instances, the merge key or keys in a DataFrame will be found in its index. In this\n",
    "case, you can pass `left_index=True` or `right_index=True` (or both) to indicate that the\n",
    "index should be used as the merge key:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left1 = pd.DataFrame(  {'key': ['a', 'b', 'a', 'a', 'b', 'c'],\n",
    "                        'value': range(6)})\n",
    "\n",
    "right1 = pd.DataFrame({'group_val': [3.5, 7]}, \n",
    "                        index=['a', 'b'])\n",
    "\n",
    "print(left1)\n",
    "print('---------')\n",
    "print(right1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left1, right1, left_on='key', right_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, since the default merge method is to intersect the join keys, you can instead form the\n",
    "union of them with an outer join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left1, right1, left_on='key', right_index=True, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame has a more **convenient join method for merging by index**. It can also be\n",
    "used to combine together many DataFrame objects **having the same or similar indexes\n",
    "but non-overlapping columns**. In this example, by merging on unique indexes, we will be performing **one-to-one merge** operations.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "right2 = pd.DataFrame(\n",
    "                     { 'group_val' : [10,20]\n",
    "                      }, index=[1,2]\n",
    "                     )\n",
    "print(left1)\n",
    "print('---------')\n",
    "print(right2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left1.join(right2, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge Exercises:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Read in the child_mortality_rates and adult_mortality_rates datasets and merge them on appropriate variables, using a meaningful merge technique. Perform data cleaning where necessary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cm = pd.read_csv('../datasets/child_mortality_rates.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Generate several plots on the above data. Is there anything interesting?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Read in the adult_mortality_rate_by_cause dataset and merge it with the above dataset on appropriate variables, using a meaningful merge technique. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "amc = pd.read_csv('../datasets/adult_mortality_rate_by_cause.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Finally, read in the total_health_expenditure_by_country_per_year dataset. Attempt to merge it with the above dataset. What are the challenges? How might you work around them? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "th = pd.read_csv('../datasets/total_health_expenditure_peercent_per_capita_of_gdp_by_country_per_year.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenating Data Frames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenation appends data frames and series objects using the `pandas.concat` method. Data frames can be appended either using the axis=0 option (default) whereby rows are added or using the axis=1, whereby columns are added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.randn(3, 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])\n",
    "df2 = pd.DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])\n",
    "\n",
    "print(df1)\n",
    "print('----------')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df2], sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The concat method appends data frames and is not concerned with creating multiple indexes. If the indexes are relevant to the data frame and it is desirable to have unique indexes, then this can be achieved as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df2], ignore_index=True, sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Create a Dataframe called df5 having 4 random float values, having a column called 'a', so that it can be appended with column 'a' from df1. Write code to concat df5 with df1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenation on axes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "concat can be used to append on the **column axis**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame(np.random.randn(2, 3), columns=['e', 'f', 'g'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df3], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `concat` method is as powerful as the merge, having a number of arguments that allow you produce custom made concatenation types. We can specify the join axes which selects the specified rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1, df3], axis=1 , join_axes=[df1.index[1:3]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updating Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Combining together values within Series or DataFrame columns from different sources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another fairly common situation is to have two like-indexed (or similarly indexed) Series or DataFrame objects and needing to “patch” values in one dataframe with values from another dataframe based on matching indices. \n",
    "\n",
    "Here is an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame([[np.nan, 3., 5.], [-4.6, np.nan, 1], [np.nan, 7., np.nan]])\n",
    "df2 = pd.DataFrame([[-42.6, np.nan, -8.2], [-5., 1.6, 4]], index=[1, 2])\n",
    "\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Say we wanted to update the values in df1, column 2 with those of df1, column 2.\n",
    "\n",
    "Our intuition might be to do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[2] = df2[2]\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the result above you will notice that all the values from df2[2] have been copied over to df1[2], and that all the existing values in df1[2] have been overwritten. In cases where the index row in df1[2] was not found in df2[2], the new value was assigned as NaN.\n",
    "\n",
    "However, this is not what we wanted. We wanted to copy the values from df2[2], but preserve the values in df1[2] that did not exist in df2[2].\n",
    "\n",
    "Let's try again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame([[np.nan, 3., 5.], [-4.6, np.nan, 1], [np.nan, 7., np.nan]])\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function that we need is called update."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[2].update(df2[2])\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that update performs its operation inplace.\n",
    "\n",
    "What if we now only wanted to update NaN values in df1 with the values in df2 and not just perform a blanket update?\n",
    "\n",
    "This can be achieved using the combine_first method.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[[0,1]].combine_first(df2[[0,1]]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that this method only takes values from the right DataFrame if they are missing in the left DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Use the datasets below and the command df = pd.read_clipboard() in order to construct dataframes for the exercises below:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "DATAFRAME 1 (df_USD1)\n",
    "\n",
    "ID    USD \tEUR \tGBP\n",
    "0 \tNaN \t289.6 \t180.1\n",
    "1 \t274.5 \t292.3 \t183.7\n",
    "2 \t276.5 \t310.5 \t189.9\n",
    "3 \t347.2 \t-0.0 \tNaN\n",
    "4 \t416.3 \t-0.0 \t232.5\n",
    "5 \t435.6 \t-0.0 \t226.9\n",
    "6 \t513.0 \t434.9 \tNaN\n",
    "7 \tNaN \t479.3 \t322.9\n",
    "8 \t833.8 \tNaN \t418.8\n",
    "9 \t869.8 \t625.7 \t604.9\n",
    "10 \t1087.5 \t757.9 \t673.4\n",
    "11 \t1405.5 \tNaN \tNaN\n",
    "12 \t1531.0 \t1179.4 \t985.1\n",
    "13 \tNaN \t1257.2 \t1019.7\n",
    "14 \t1204.5 \t871.9 \t728.9\n",
    "15 \t1182.4 \t1092.8 \t790.8\n",
    "\n",
    "\n",
    "DATAFRAME 2 (df_USD2)\n",
    "\n",
    "ID    USD \tEUR \tGBP\n",
    "0 \t290.2 \t289.6 \t180.1\n",
    "1 \t274.5 \t292.3 \t183.7\n",
    "2 \t276.5 \t310.5 \t189.9\n",
    "3 \t347.2 \t330.9 \t215.7\n",
    "4 \t416.3 \t330.0 \t232.5\n",
    "5 \t435.6 \t320.5 \t226.9\n",
    "6 \t513.0 \t434.9 \t298.8\n",
    "7 \t632.0 \t479.3 \t322.9\n",
    "8 \t833.8 \t570.3 \t418.8\n",
    "9 \t869.8 \t625.7 \t604.9\n",
    "10 \t1087.5 \t757.9 \tNaN\n",
    "11 \t1405.5 \t1047.7 \t897.7\n",
    "12 \t1531.0 \t1179.4 \t985.1\n",
    "13 \t1657.5 \t1257.2 \t1019.7\n",
    "14 \t1204.5 \t871.9 \tNaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** df_USD1 has missing values for the USD and GBP. Populate the missing values with those from the dataframe df_USD2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** df_USD1 has a combination of missing values and erroneous values for the EUR column. Replace all the values in this columns with those that exist in dataframe df_USD2 for this column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
